package veilingsite.dao.nl;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Comparator;

import veilingsite.beans.nl.BodBean;
import veilingsite.beans.nl.VeilingBean;
import veilingsite.database.nl.DBmanager;

public class VeilingDao {
	private String sqlCreateBiedingenTable = "CREATE TABLE bod(bod FLOAT NOT NULL, datum Timestamp NOT NULL, " +
			"veilingId INT NOT NULL, biederId INT NOT NULL, biederNaam VARCHAR(100), veilingNaam VARCHAR(100) )";
	private String sqlCreateVeilingTable = "CREATE TABLE veiling(veilingId INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY " +
			"(START WITH 1, INCREMENT BY 1), startPrijs float, eigenaarId INT NOT NULL," +
			"veilingNaam VARCHAR(50), beschrijving VARCHAR(32672), imageLink VARCHAR(300), beginDatum Timestamp," +
			"sluitingsDatum Timestamp, isVoorbij BOOLEAN NOT NULL DEFAULT  false)"; 
	
	private String sqlCreateVeiling = "INSERT INTO veiling ( startPrijs, eigenaarId, veilingNaam, beschrijving" +
			", imageLink, beginDatum, sluitingsDatum) VALUES (?,?,?,?,?,?,?)";
	private String sqlCreateBod = "INSERT INTO bod VALUES (?,?,?,?,?,?)";
	
	
	private String sqlGetVeilingDoorId = "SELECT * FROM veiling WHERE veiling.veilingId = ?";
	private String sqlGetVeilingenVanGebruiker = "SELECT * FROM veiling WHERE veiling.eigenaarID = ?";
	private String sqlGetBiedingenVanGebruiker = "SELECT * FROM bod WHERE bod.biederId = ?";
	private String sqlGetBiedingenVanVeiling = "SELECT bod.bod, bod.datum, bod.veilingId, bod.biederId, bod.biederNaam, bod.veilingNaam FROM bod,veiling WHERE veiling.veilingId = ? AND" +
			" bod.veilingId = ?";
	private String sqlGetVeilingLijst = "SELECT * FROM veiling";
	private String sqlGetHoogsteBodVanVeiling = "SELECT * FROM bod WHERE bod.veilingId =?";
	
	private String sqlStopVeiling = "UPDATE veiling SET veiling.isVoorbij=? WHERE veiling.veilingId =? AND veiling.eigenaarId =?";
	private String sqlDeleteVeiling = "DELETE FROM veiling WHERE veiling.veilingId=? AND veiling.eigenaarId=?"; 
	private String sqlDeleteBod = "DELETE FROM bod WHERE bod.veilingId =?";
	
	private Connection conn;
	private Statement stmt;
	private PreparedStatement pstmtCreateVeiling = null;
	private PreparedStatement pstmtGetVeilingDoorId = null;
	private PreparedStatement pstmtGetVeilingLijst = null;
	private PreparedStatement pstmtGetVeilingenVanGebruiker = null;
	private PreparedStatement pstmtGetBiedingenVanGebruiker = null;
	private PreparedStatement pstmtGetBiedingenVanVeiling = null;
	private PreparedStatement pstmtGetHoogsteBodVanVeiling = null;
	private PreparedStatement pstmtCreateBod = null;
	private PreparedStatement pstmtStopVeiling = null;
	private PreparedStatement pstmtDeleteVeiling = null;
	private PreparedStatement pstmtDeleteBod = null;
	
	
	public VeilingDao(DBmanager db){
		conn = db.getConnection();
		
	}
	
	public void init(){
		try {
			pstmtGetVeilingLijst = conn.prepareStatement(sqlGetVeilingLijst);
			pstmtGetVeilingDoorId = conn.prepareStatement(sqlGetVeilingDoorId);
			pstmtCreateVeiling = conn.prepareStatement(sqlCreateVeiling);
			pstmtGetVeilingenVanGebruiker = conn.prepareStatement(sqlGetVeilingenVanGebruiker);
			pstmtStopVeiling = conn.prepareStatement(sqlStopVeiling);
			pstmtCreateBod = conn.prepareStatement(sqlCreateBod);
			pstmtGetBiedingenVanVeiling = conn.prepareStatement(sqlGetBiedingenVanVeiling);
			pstmtDeleteVeiling = conn.prepareStatement(sqlDeleteVeiling);
			pstmtDeleteBod = conn.prepareStatement(sqlDeleteBod);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Creates the bod and veiling tables if they don't exist
	 */
	public void createTablesIfNeeded(){
		try {
			if (!checkIfBodTableExists()){
				stmt = conn.createStatement();
				stmt.execute(sqlCreateBiedingenTable);
			}
			
			if (!checkIfVeilingTableExists()){
				stmt = conn.createStatement();
				stmt.execute(sqlCreateVeilingTable);			
			}
			
		} catch (SQLException e){
			e.printStackTrace();
		}
	}
	
	public boolean createVeiling(double startPrijs, int eigenaarId, String veilingNaam, 
			String beschrijving, String imageLink, Timestamp beginDatum, Timestamp sluitingsDatum){
		try {
			
			pstmtCreateVeiling.setDouble(1, startPrijs );
			pstmtCreateVeiling.setInt(2, eigenaarId );
			pstmtCreateVeiling.setString(3, veilingNaam );
			pstmtCreateVeiling.setString(4, beschrijving );
			pstmtCreateVeiling.setString(5, imageLink );
			pstmtCreateVeiling.setTimestamp(6, beginDatum );
			pstmtCreateVeiling.setTimestamp(7, sluitingsDatum );
			
			pstmtCreateVeiling.execute();		
			return true;
			
		} catch (SQLException e){
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean checkIfBodTableExists(){
		try {
			DatabaseMetaData dbd = conn.getMetaData();
			ResultSet rs = dbd.getTables(null, null, null, new String[]{"TABLE"});
			while (rs.next()){
				if(rs.getString("TABLE_NAME").equalsIgnoreCase("bod")){
					return true;
				}
			}
			
		} catch (SQLException e) {
			return false;
		}
		
		return false;
	}
	
	public boolean checkIfVeilingTableExists(){
		try {
			DatabaseMetaData dbd = conn.getMetaData();
			ResultSet rs = dbd.getTables(null, null, null, new String[]{"TABLE"});
			while (rs.next()){
				if(rs.getString("TABLE_NAME").equalsIgnoreCase("veiling")){
					return true;
				}
			}
			
		} catch (SQLException e) {
			return false;
		}
		
		return false;
	}
	
	public VeilingBean getVeiling(int veilingId) {
		VeilingBean veiling = new VeilingBean();
		try {
			pstmtGetVeilingDoorId.setInt(1, veilingId);
			ResultSet rs = pstmtGetVeilingDoorId.executeQuery();
			
			while (rs.next()) {
				veiling = getVeilingBean(rs);
			}
			veiling.setBiedingen(getBiedingenVanVeiling(veiling.getId()));
			
			return veiling;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public ArrayList<VeilingBean> getVeilingenVanDezeWeek(){
		try {
			
			ResultSet rs = pstmtGetVeilingLijst.executeQuery();
			
			ArrayList<VeilingBean> veilingen = new ArrayList<VeilingBean>();
			
			// maak een timeStamp object van een week geleden
			Calendar c = Calendar.getInstance();
			c.add(Calendar.DAY_OF_YEAR, +7);
			Timestamp nextWeek = new Timestamp(c.getTime().getTime());
			Timestamp currentTime = new Timestamp(System.currentTimeMillis());
			
			while(rs.next()){
				// als het over minder dan een week afloopt
				if (rs.getTimestamp(8).before(nextWeek)){
					// als de veilingen nog niet afgelopen zijn
					if (!rs.getTimestamp(8).before(currentTime) && !! !rs.getBoolean(9)){
					VeilingBean tmpVeiling = getVeilingBean(rs);
					tmpVeiling.setBiedingen(getBiedingenVanVeiling(tmpVeiling.getId()));
					veilingen.add(tmpVeiling);
					}
				}
				
			}
			Collections.sort(veilingen, new TimestampComparator());
			
			return veilingen;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
		
	}
	
	/**
	 * Get a VeilingBean out of a result set
	 * @param rs a result set with the data for a VeilingBean
	 * @return VeilingBean
	 */
	private VeilingBean getVeilingBean(ResultSet rs){
		VeilingBean veilingBean = new VeilingBean();
		try {
			veilingBean.setVeilingId(rs.getInt(1));
			veilingBean.setStartPrijs(rs.getDouble(2));
			veilingBean.setEigenaarId(rs.getInt(3));
			veilingBean.setVeilingNaam(rs.getString(4));
			veilingBean.setBeschrijving(rs.getString(5));
			veilingBean.setImageLink(rs.getString(6));
			veilingBean.setBeginDatum(rs.getTimestamp(7));
			veilingBean.setSluitingsDatum(rs.getTimestamp(8));
			veilingBean.setIsVoorbij(rs.getBoolean(9));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return veilingBean;
	}
	
	public ArrayList<VeilingBean> getMijnVeilingen(int gebruikerId){
		try {
			
			pstmtGetVeilingenVanGebruiker.setInt(1, gebruikerId);
			ResultSet rs = pstmtGetVeilingenVanGebruiker.executeQuery();
			
			ArrayList<VeilingBean> veilingen = new ArrayList<VeilingBean>();
			
			while(rs.next()){
				veilingen.add(getVeiling(rs.getInt(1))); 
			}
			return veilingen;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public ArrayList<BodBean> getBiedingenVanGebruiker(int gebruikerId){
		try {
			pstmtGetBiedingenVanGebruiker = conn.prepareStatement(sqlGetBiedingenVanGebruiker);
			pstmtGetBiedingenVanGebruiker.setInt(1, gebruikerId);
			ResultSet rs = pstmtGetBiedingenVanGebruiker.executeQuery();
			
			ArrayList<BodBean> biedingen = new ArrayList<BodBean>();
			
			while(rs.next()){
				biedingen.add(getBodBean(rs));
			}
			
			return biedingen;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	
	public int stopVeiling(int veilingId, int userId) {
		try {
			pstmtStopVeiling.setBoolean(1, true);
			pstmtStopVeiling.setInt(2, veilingId);
			pstmtStopVeiling.setInt(3, userId);
			pstmtStopVeiling.execute();
			
			VeilingBean checkVeiling = getVeiling(veilingId);
			if (checkVeiling.getIsVoorbij()){
				return 0;
			} else {
				return -1;
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			return -2;
		}
	}
	
	public boolean deleteVeiling(int veilingId, int userId) {
		boolean result = false;
		try {
			pstmtDeleteVeiling.setInt(1, veilingId);
			pstmtDeleteVeiling.setInt(2, userId);
			pstmtDeleteVeiling.execute();
			
			VeilingBean checkVeiling = getVeiling(veilingId);
			if (checkVeiling.getId() == 0){
				result = true;
			} else {
				result = false;
			}
			
			if (deleteBiedingenVanVeiling(veilingId)){
				result = true;
			} else {
				result = false;
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	private boolean deleteBiedingenVanVeiling(int veilingId){
			try {
				pstmtDeleteBod.setInt(1, veilingId);
				pstmtDeleteBod.execute();
				return true;
			} catch (SQLException e) {
				return false;
			}
	}
	
	
	public ArrayList<BodBean> getBiedingenVanVeiling(int veilingId){
		try {
			pstmtGetBiedingenVanVeiling.setInt(1, veilingId);
			pstmtGetBiedingenVanVeiling.setInt(2, veilingId);
			ResultSet rs = pstmtGetBiedingenVanVeiling.executeQuery();
			ArrayList<BodBean> biedingen = new ArrayList<BodBean>();
			
			while(rs.next()){
				biedingen.add(getBodBean(rs));
			}
			
			return biedingen;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public BodBean getHoogsteBodVanVeiling(int veilingId){
		
		try {
			BodBean hoogsteBod = new BodBean();
			hoogsteBod.setBod(0);
			 pstmtGetHoogsteBodVanVeiling =  conn.prepareStatement(sqlGetHoogsteBodVanVeiling);
			 pstmtGetHoogsteBodVanVeiling.setInt(1, veilingId);
			
			 ResultSet rs = pstmtGetHoogsteBodVanVeiling.executeQuery();
			 
			 while(rs.next()){
				 if (rs.getDouble(1) > hoogsteBod.getBod()){
					 hoogsteBod = getBodBean(rs);
				 }
			 }
			 return hoogsteBod;
			 
		} catch (SQLException e){
			e.printStackTrace();
		}
		return null;
	}
	
	private BodBean getBodBean(ResultSet rs){
		BodBean bodBean = new BodBean();
		try {
			bodBean.setAll(rs.getDouble(1), rs.getTimestamp(2), rs.getInt(3), 
					rs.getInt(4), rs.getString(5),rs.getString(6));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return bodBean;
	}
	
	public void voegNieuwBodToe(double bod, Timestamp newTimestamp, int veilingId, int biederId, 
			String biederNaam, String veilingNaam){
		try {
			pstmtCreateBod.setDouble(1, bod);
			pstmtCreateBod.setTimestamp(2, newTimestamp);
			pstmtCreateBod.setInt(3, veilingId);
			pstmtCreateBod.setInt(4, biederId);
			pstmtCreateBod.setString(5,biederNaam);
			pstmtCreateBod.setString(6, veilingNaam);
			pstmtCreateBod.execute();
			
		} catch (SQLException e){
			e.printStackTrace();
		}
	}
	
	class TimestampComparator implements Comparator<VeilingBean>{
		   
	    public int compare(VeilingBean veiling1, VeilingBean veiling2){
	       
	    	
	        Timestamp veiling1SluitingsDatum = veiling1.getSluitingsDatum();
	        Timestamp veiling2SluitingsDatum = veiling2.getSluitingsDatum();
	       
	        return veiling1SluitingsDatum.compareTo(veiling2SluitingsDatum);
	    }
	   
	}



}
